<?php if (!defined('BASEPATH')) exit('No direct script access allowed'); 

// @TODO: Get results only where they are set to active

class Listings_model extends Model {

    var $entries_table 		= 'entries'; 	// Entries table
    var $categories_table 	= 'categories'; 	// Category table
    var $posters_table          = 'posters';
    var $entries_posters        = 'entries_posters';

    /**
     * Constructor
     *
     * @access	public
     */
    function Listings_model() {
        parent::Model();
    }

    // =============================
    // = ========== GET ========== =
    // =============================

    /**
     * Get all Categories
     *
     * @access	public
     * @return 	object	Categories
     */
    function get_all_categories() {
        $query = $this->db->get($this->categories_table);
        return $query->result();
    }

    // --------------------------------------------------------------------

    /**
     * Get all entries
     *
     * @access	public
     * @return 	object	Entries
     */
    function get_all_entries() {
        $query = $this->db->get($this->entries_table);
        return $query->result();
    }

    // --------------------------------------------------------------------

    /**
     * Get Category by its ID
     *
     * @access	public
     * @param	integer	Category ID
     * @return 	object	Category
     */
    function get_category($id) {
        $query = $this->db->get_where($this->categories_table, array('category_id' => $id));
        return $query->row();
    }

    // --------------------------------------------------------------------

    /**
     * Get a single entry by its ID
     *
     * @access	public
     * @param	integer	Entry ID
     * @return 	object	Entry
     */
    function get_entry($id) {
        $query = $this->db->get_where($this->entries_table, array('entry_id' => $id));
        return $query->row();
    }

    // --------------------------------------------------------------------

    /**
     * Get all listing of a Category by its ID - Category is an object
     *
     * @access	public
     * @param	integer	Category ID
     * @return 	mixed	Category and Entries
     */
    function get_category_listings_from_to($id, $from , $to, $limit = FALSE, $offset = FALSE) {
        $from = $from . " 00:00:00"; //date format: '2004-10-26 00:00:00'
        $to = $to . " 23:59:59";
        // Ambiguous Fields, entries, categories
        $this->db->select('c.description as c_description, e.description as e_description, ');
        $this->db->select('entry_id, FK_category_id, active, title, date_added');
        $this->db->select('category_id, name,name_gr, entry_count');

        $this->db->from($this->categories_table.' AS c');
        $this->db->join($this->entries_table.' AS e', 'c.category_id = e.FK_category_id', 'left');

        $this->db->where(array('c.category_ID'=>$id,'e.date_added >'=> $from , 'e.date_added <' => $to));
        $this->db->order_by('e.date_added','desc');
        $this->db->limit($limit);
        $this->db->offset($offset);

        $query = $this->db->get();
        // echo $this->db->last_query();
        //     exit();
        // Convenience - they aren't really related in this way
        $result = array(
            'entries'	=> $query->result(),
            'category'	=> $query->row()
        );

        // Free results
        $query->free_result();

        return $result;
    }

    // --------------------------------------------------------------------

    /**
     * Get new entries
     *
     * @access	public
     */
    function get_new_entries($limit = '10', $category = FALSE) {
        if($category) {
            $this->db->where('FK_category_id', $category);
        }

        $this->db->limit($limit);
        $this->db->order_by('date_added', 'desc');

        $query = $this->db->get($this->entries_table);
        return $query->result();
    }

    // --------------------------------------------------------------------

    /**
     * Get all entries for a user
     *
     * @access	public
     * @param	integer	user id
     */
    function get_user_entries($id) {
        $this->db->select($this->categories_table.'.description AS cat_description, '.$this->entries_table.'.description, name, entry_id, title');
        $this->db->join($this->categories_table, $this->entries_table.'.FK_category_id = '.$this->categories_table.'.category_id', 'left');

        //   $query = $this->db->get_where($this->entries_table, array('FK_user_id' => $id));
        return $query->result();
    }

    // =============================
    // = ========== ADD ========== =
    // =============================

    /**
     * Add a new entry
     *
     * @access	public
     */
    function add_entry() {
        $category = $this->input->post('category');


        $fields = array(
            'FK_category_id'            =>	$category,
            'title'			=>	$this->input->post('title'),
            'description'		=>	$this->input->post('description'),
            'description_gr'		=>	$this->input->post('description_gr'),
            'price'                     =>	$this->input->post('price'),
        );

        $this->db->set($fields);
        $this->db->insert($this->entries_table);
        $id = $this->db->insert_id();

        $this->db->set('entry_count', 'entry_count+1', FALSE);
        $this->db->where('category_id', $category);
        $this->db->update($this->categories_table);

        $fields = array(
            'p_email'     => $this->input->post('email'),
            'p_full_name' => $this->input->post('full_name'),
        );
        $this->db->set($fields);
        $this->db->insert($this->posters_table );
        $pid = $this->db->insert_id();
        $fields = array(
            '`FK_entry_id`'    => $id,
            '`FK_poster_id`'   => $pid,
            '`from`'           => $this->input->post('date3'),
            '`to`'             => $this->input->post('date4')
        );
        $this->db->set($fields);
        $this->db->insert($this->entries_posters  );
        $pid = $this->db->insert_id();
        return $id;
    }

    // --------------------------------------------------------------------

    /**
     * Add a new category
     *
     * @access	public
     */
    function add_category() {
        $fields = array(
            'name'		=> $this->input->post('name'),
            'description'	=> $this->input->post('description'),
            'entry_count'	=> '0'
        );

        $this->db->set($fields);
        $this->db->insert($this->categories_table);

        return $this->db->insert_id();
    }

    // ================================
    // = ========== UPDATE ========== =
    // ================================

    /**
     * Update an existing entry
     *
     * @access	public
     */
    function update_entry($id, $old_cat) {
        $category = $this->input->post('category');

        $fields = array(
            'FK_category_id'	=>	$category,
            'title'		=>	$this->input->post('title'),
            'description'	=>	$this->input->post('description'),
            'description_gr'	=>	$this->input->post('description_gr'),
            'price'             =>	$this->input->post('price')
        );

        $this->db->set($fields);
        $this->db->where('entry_id', $id);
        $this->db->update($this->entries_table);

        // Did the category change?
        if ($old_cat != $category) {
            $this->db->set('entry_count', 'entry_count-1', FALSE);
            $this->db->update($this->categories_table, NULL, "category_id = ".$old_cat);

            $this->db->set('entry_count', 'entry_count+1', FALSE);
            $this->db->update($this->categories_table, NULL, "category_id = ".$category);
        }
    }

    // --------------------------------------------------------------------

    /**
     * Update an existing category
     *
     * @access	public
     */
    function update_category($id) {
        $fields = array(
            'name'			=>	$this->input->post('name'),
            'description'		=>	$this->input->post('description')
        );

        $this->db->set($fields);
        $this->db->where('category_id', $id);
        $this->db->update($this->categories_table);
    }

    // ================================
    // = ========== DELETE ========== =
    // ================================

    /**
     * Delete a Category
     *
     * @access	public
     * @param	integer	entry id
     */
    function delete_category($id) {
        $this->db->delete( $this->categories_table, array('category_id' => $id) );
    }

    // --------------------------------------------------------------------

    /**
     * Delete an Entry
     *
     * @access	public
     * @param	integer	entry id
     */
    function delete_entry($id) {
        $query = $this->db->get_where( $this->entries_table, array('entry_id' => $id) );

        if($query->num_rows() > 0) {
            $result = $query->row();

            $this->db->trans_start();

            // Delete
            $this->db->delete( $this->entries_table, array('entry_id' => $id) );

            // Decrement Child Count
            $this->db->set('entry_count', 'entry_count-1', FALSE);
            $this->db->where('category_id', $result->FK_category_id);
            $this->db->update($this->categories_table);

            $this->db->trans_complete();
        }
        else {
            die('entry does not exist, or does not have a category - remove it manually');
        }
    }

    // --------------------------------------------------------------------

    /**
     * Delete all of a user's entries
     *
     * @access	public
     * @param	integer	entry id
     */
    function delete_user_entries($id) {
        $entries = $this->get_user_entries($id);

        foreach($entries as $entry) {
            $this->delete_entry($entry->entry_id);
        }
    }

    // ================================
    // = ========== OTHERS ========== =
    // ================================

    /**
     * Check if a category exists
     *
     * @access	public
     * @param	string	category name
     * @return 	bool
     */
    function check_category($name) {
        $query = $this->db->get_where($this->categories_table, array('name' => $name));
        return ($query->num_rows() > 0) ? TRUE : FALSE;
    }
}

// END listings_model.php
/* Location: ./application/models/listings_model.php */